package th.kmutt.se.is.fact;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class Database {
	public static final String DB_NAME = "APARTMENT";
	public static final int DB_VERSION = 1;
	
	public static final String TB_ROOM = "ROOMS";//
	public static final String TB_TENANT = "TENANTS";//
	public static final String TB_CHARGES = "CHARGES";//
	public static final String TB_RENTAL = "RENTALS";//

	public static final String TB_R_TENANT = "ROOM_TENANTS";//
	public static final String TB_R_CHARGE = "ROOM_CHARGE";
	public static final String TB_R_CD = "ROOM_TENANTS";
	
	public static final String KEY_ID = "ID";
	public static final String KEY_NAME = "NAME";
	public static final String KEY_FL = "FLOOR";
	public static final String KEY_PRICE = "PRICE";
	public static final String KEY_DPRICE = "DEFAULT_PRICE";
	public static final String KEY_UPRICE = "UNIT_PRICE";
	public static final String KEY_DEP_PRICE = "DEPOSIT_PRICE";
	public static final String KEY_DEP_PAID = "DEPOSIT_PAID";
	public static final String KEY_METER_W = "METER_WATER";
	public static final String KEY_METER_E = "METER_ELECT";
	
	public static final String KEY_STATUS = "STAUS";

	public static final String KEY_SURNAME = "SURNAME";
	public static final String KEY_PNO = "PERSONAL_NO";
	public static final String KEY_PHONE = "PHONE";
	public static final String KEY_EMAIL = "EMAIL";
	public static final String KEY_UNIT = "UNIT_USE";
	public static final String KEY_TYPE = "CHARGE_TYPE";
	public static final String KEY_DESC = "DESCRIPTION";
	

	public static final String KEY_MONTH = "MONTH";
	public static final String KEY_YEAR = "YEAR";
	public static final String KEY_RID = "ROOM_ID";
	public static final String KEY_RPRICE = "ROOM_PRICE";
	public static final String KEY_TPRICE = "TOTAL_PRICE";
	public static final String KEY_PAIDPRC = "PAID_PRICE";
	public static final String KEY_PAIDDATE = "PAID_DATE";

	public static final String KEY_TID = "TENANT_ID";
	public static final String KEY_SIGNDATE = "SIGNED_DATE";
	public static final String KEY_TEMIDATE = "TERMINATE_DATE";

	public static final String KEY_CID = "CHARGE_ID";
	public static final String KEY_RTID = "RENTAL_ID";
	

	private static final String SCR_TB_ROOM = "CREATE TABLE "
			+ TB_ROOM + " (" 
			+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ KEY_NAME + " TEXT, " 
			+ KEY_FL + " INTEGER, "
			+ KEY_PRICE + " DOUBLE, "
			+ KEY_DEP_PRICE + " DOUBLE, "
			+ KEY_DEP_PAID + " DOUBLE, "
			+ KEY_METER_W + " INTEGER, "
			+ KEY_METER_E + " INTEGER, "
			+ KEY_STATUS + " INTEGER);";

	private static final String SCR_TB_TENANT = "CREATE TABLE "
			+ TB_TENANT + " (" 
			+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ KEY_TYPE + " INTEGERi8, " 
			+ KEY_NAME + " TEXT, "
			+ KEY_SURNAME + " TEXT, " 
			+ KEY_PNO + " TEXT, "
			+ KEY_PHONE + " TEXT, "
			+ KEY_EMAIL + " TEXT, "
			+ KEY_SIGNDATE + " TEXT,"
			+ KEY_TEMIDATE + " TEXT,"
			+ KEY_STATUS + " INTEGER);";

	private static final String SCR_TB_CHARGES = "CREATE TABLE "
			+ TB_CHARGES + " (" 
			+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ KEY_TYPE + " INTEGER, " 
			+ KEY_NAME + " TEXT, "
			+ KEY_DESC + " TEXT, "
			+ KEY_DPRICE + " DOUBLE, "
			+ KEY_STATUS + " INTEGER);";

	private static final String SCR_TB_RENTAL = "CREATE TABLE "
			+ TB_RENTAL + " (" 
			+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ KEY_RID + " INTEGER, "
			+ KEY_MONTH + " INTEGER, " 
			+ KEY_YEAR + " INTEGER, "
			+ KEY_RPRICE + " DOUBLE, " 
			+ KEY_TPRICE + " DOUBLE, " 
			+ KEY_PAIDPRC + " DOUBLE, "
			+ KEY_PAIDDATE + " TEXT, "
			+ KEY_STATUS + " INTEGER);";

	private static final String SCR_TB_R_CHARGE = "CREATE TABLE "
			+ TB_R_CHARGE + " ("  
			+ KEY_RID + " INTEGER, "
			+ KEY_CID + " INTEGER, " 
			+ KEY_PRICE + " DOUBLE);";

	private static final String SCR_TB_R_CD = "CREATE TABLE "
			+ TB_R_CD + " ("  
			+ KEY_RTID + " INTEGER, "
			+ KEY_CID + " INTEGER, " 
			+ KEY_UNIT + " INTEGER, " 
			+ KEY_UPRICE + " DOUBLE, " 
			+ KEY_PRICE + " DOUBLE);";
	
	
	
	private SQLiteHelper sqLiteHelper;
	public SQLiteDatabase sqLiteDatabase;

	private Context context;

	public Database(Context c) {
		context = c;
	}

	public Database openToRead() throws android.database.SQLException {
		sqLiteHelper = new SQLiteHelper(context, DB_NAME, null, DB_VERSION);
		sqLiteDatabase = sqLiteHelper.getReadableDatabase();
		return this;
	}

	public Database openToWrite() throws android.database.SQLException {
		sqLiteHelper = new SQLiteHelper(context, DB_NAME, null, DB_VERSION);
		sqLiteDatabase = sqLiteHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		sqLiteHelper.close();
	}

	public class SQLiteHelper extends SQLiteOpenHelper {

		public SQLiteHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(SCR_TB_ROOM);
			db.execSQL(SCR_TB_TENANT);
			db.execSQL(SCR_TB_CHARGES);
			db.execSQL(SCR_TB_RENTAL);
			db.execSQL(SCR_TB_R_CHARGE);
			db.execSQL(SCR_TB_R_CD);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
	}

}
